package JDBC;

import org.junit.Test;

import java.sql.*;

public class A {
    public static void main(String[] args) {

    }
    @Test
    public void add() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        //1加载驱动到内存中去
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2建立连接
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/bz?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");

            //3执行sql语句
            String sql="insert into person(username,password) values(?,?)";//一个问号代表一个值
            pstmt=conn.prepareStatement(sql);//预处理sql语句
            //*预处理是为了事先将该sql语句放入到内存中，当在遇到有相同的sql语句的时候，
            //就不用再去查询了，直接返回结果给用户
            pstmt.setString(1,"Ronaldo" );
            pstmt.setString(2,"07");

            System.out.println(pstmt.executeUpdate()>0?"add successful！":"add fail!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            //4关闭连接
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    @Test
    public void delete() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        //1加载驱动到内存中去
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2建立连接
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/bz?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");

            //3执行sql语句
//            String sql="insert into person(username,password) values(?,?)";//一个问号代表一个值
            String sql="delete from person where username=? ";
            pstmt=conn.prepareStatement(sql);//预处理sql语句
            //*预处理是为了事先将该sql语句放入到内存中，当在遇到有相同的sql语句的时候，
            //就不用再去查询了，直接返回结果给用户
            pstmt.setString(1,"Ronaldo" );
            System.out.println(pstmt.executeUpdate()>0?"delete successful！":"delete fail!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            //4关闭连接
            if (pstmt != null) {

                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    @Test
    public void update() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        //1加载驱动到内存中去
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2建立连接
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/bz?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");

            //3执行sql语句
//            String sql="insert into person(username,password) values(?,?)";//一个问号代表一个值
            String sql="update person set username=?,password=? where id=?";
            pstmt=conn.prepareStatement(sql);//预处理sql语句
            //*预处理是为了事先将该sql语句放入到内存中，当在遇到有相同的sql语句的时候，
            //就不用再去查询了，直接返回结果给用户
            pstmt.setString(1,"Xavi" );
            pstmt.setString(2,"06" );
            pstmt.setInt(3,20);
            System.out.println(pstmt.executeUpdate()>0?"update successful！":"update fail!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            //4关闭连接
            if (pstmt != null) {

                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    @Test
    public void quary() {
        Connection conn=null;
        PreparedStatement pstmt=null;//过后一定要关闭
        ResultSet rs=null;//用于存结果，过后一定要关闭
        //1加载驱动到内存中去
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2建立连接
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/bz?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");

            //3执行sql语句
//            String sql="insert into person(username,password) values(?,?)";//一个问号代表一个值
            String sql="select * from person where username like ?";
            pstmt=conn.prepareStatement(sql);//预处理sql语句
            pstmt.setString(1,"深%");
            //*预处理是为了事先将该sql语句放入到内存中，当在遇到有相同的sql语句的时候，
            //就不用再去查询了，直接返回结果给用户

            //4检索结果
            rs=pstmt.executeQuery();
            if(rs.next()){//如果有结果的话，就输出
                System.out.println(rs.getString("username")+" "+rs.getString("password"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            //5关闭连接 关闭顺序按后进先出来操作，先声明的后关闭
            if (rs != null) {

                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {

                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
